﻿using Beryl.Util;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;


namespace Beryl.OrderControllers
{
    public class CuttingPlan
    {
        private static CuttingPlan instance = null;
        private static readonly object uniqueLock = new object();
        public static CuttingPlan Instance
        {
            get
            {
                lock (uniqueLock)
                {
                    if (instance == null)
                    {
                        instance = new CuttingPlan();
                    }
                }
                return instance;
            }
        }
        public CuttingPlan() { }

        //数据查询
        public List<string> CuttingPlanQuerys(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string value1 = k["value1"].ToString();
                string value2 = k["value2"].ToString();
                string value3 = k["value3"].ToString();
                string value4 = k["value4"].ToString();
                string value5 = k["value5"].ToString();
                string plot = k["plot"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();

                DateTime STime = DateTime.Parse(value3);
                DateTime Etime = DateTime.Parse(value4);

                string StimeString = STime.ToString("yyyy-MM-dd HH:mm:ss");
                string EtimeString = Etime.ToString("yyyy-MM-dd HH:mm:ss");

                if (Etime >= STime)
                {
                    var sqlstr = "";
                    if (value1 == "" && value2 == "" && value5 == "全部")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_ProLotOrder.cProductDepNo = '{0}'
                                AND dMakePlanDate >= '{1}'
                                AND dMakePlanDate <= '{2}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", cProductDepNo, StimeString, EtimeString);

                    }
                    else if (value1 != "" && value2 == "" && value5 == "全部")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_ProLotOrder.cMOLotNo = '{0}'
                                AND APS_ProLotOrder.cProductDepNo = '{1}'
                                AND dMakePlanDate >= '{2}'
                                AND dMakePlanDate <= '{3}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", value1, cProductDepNo, StimeString, EtimeString);
                    }
                    else if (value1 == "" && value2 != "" && value5 == "全部")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                APS_ProLotOrder.cMatNo = '{0}'
                                AND APS_ProLotOrder.cProductDepNo = '{1}'
                                AND dMakePlanDate >= '{2}'
                                AND dMakePlanDate <= '{3}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", value2, cProductDepNo, StimeString, EtimeString);

                    }
                    else if (value1 == "" && value2 == "" && value5 != "全部")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_MatData_V.cMatName like '%{0}'
                                AND APS_ProLotOrder.cProductDepNo = '{1}'
                                AND dMakePlanDate >= '{2}'
                                AND dMakePlanDate <= '{3}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", value5, cProductDepNo, StimeString, EtimeString);

                    }
                    else if (value1 != "" && value2 == "" && value5 != "全部")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_ProLotOrder.cMOLotNo = '{0}'
                                AND APS_MatData_V.cMatName like '%{1}'
                                AND APS_ProLotOrder.cProductDepNo = '{2}'
                                AND dMakePlanDate >= '{3}'
                                AND dMakePlanDate <= '${4}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", value1, value5, cProductDepNo, StimeString, EtimeString);

                    }
                    else if (value1 != "" && value2 != "" && value5 != "全部")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_ProLotOrder.cMOLotNo = '{0}'
                                APS_ProLotOrder.cMatNo = '{1}'
                                AND APS_MatData_V.cMatName like '%{2}'
                                AND APS_ProLotOrder.cProductDepNo = '{3}'
                                AND dMakePlanDate >= '{4}'
                                AND dMakePlanDate <= '{5}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", value1, value2, value5, cProductDepNo, StimeString, EtimeString);

                    }
                    if (plot != "")
                    {
                        sqlstr = string.Format(@"select
                                APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                APS_ProLotOrder.cxx,
                                APS_ProLotOrder.csx,
                                case
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)>= 0 THEN(APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                APS_MatData_V.cSpecifucation,
                                APS_MatData_V.cCateNo,
                                APS_MatData_V.cMatName,
                                a.pnum
                            FROM APS_ProLotOrder
                                LEFT JOIN(SELECT cOrderNo, SUM(dReqQty) as dAllReqQty, SUM(dPlanQty * dWidth) as dEffWidth, sum(iCutPrintNum) as pnum FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatData_V ON APS_ProLotOrder.cMatNo = APS_MatData_V.cMatNo
                            WHERE
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_ProLotOrder.cBatchNumber = '{0}'
                                AND APS_ProLotOrder.cProductDepNo = '{1}'
                                order by APS_ProLotOrder.cMOLotNo desc; ", plot, cProductDepNo);
                    }
                    string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sqlstr));
                    if (strPicYZ.Equals("[]"))
                    {
                        throw new Exception("数据为空");
                    }
                    list.Add(strPicYZ);
                }
                else
                {
                    throw new Exception("结束日期小于开始日期,请重新选择!");
                }
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //人工选择已切首次进入有数据
        public List<string> FirstLoadData(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"SELECT
                	a.cMOLotNo,
                	b.cMatandResNo as cMatNo,
                	APS_MatData_V.cMatName,
                	a.cBegProDate,
                	a.cEndProDate,
                	NULLIF ( b.dPlanQty, 0 ) * NULLIF ( b.dWidth, 0 ) AS dEffWidth,
                	b.cSpecifution as cSpecifucation,
                	b.dReqQty,
                	c.dReqQty1,
                	c.dfReqQty,
                	ISNULL(c.dReqQty1,0) - ISNULL(c.dfReqQty,0) as cha,
                	NULLIF ( b.dPlanQty, 0 ) AS dPlanQty,
                	b.dWidth,
                	NULLIF ( b.dLength, 0 ) AS dLength,
                	(b.cCapacityLow + '-' + b.cCapacityUp) as cCapacity,
                	b.cCutType,
                	b.iCutPrintNum
                FROM
                	APS_ProLotOrder a
                	LEFT JOIN APS_MatData_V ON a.cMatNo = APS_MatData_V.cMatNo
                	LEFT JOIN (
                	SELECT
                		cOrderNo,
                		cMatandResNo,
                		SUM ( dReqQty ) AS dReqQty,
                		SUM ( dfReqQty ) AS dfReqQty,
                		SUM(dPlanQty) AS dPlanQty,
                		cSpecifution,
                		dWidth,
                		dLength,
                		cCapacityLow,
                		cCapacityUp,
                		cCutType,
                		iCutPrintNum
                	FROM
                		APS_ValidBOM 
                	where cOrderType = '3'
                	GROUP BY
                		cOrderNo,
                		cMatandResNo,
                		cSpecifution,
                		dWidth,
                		dLength,
                		cCapacityLow,
                		cCapacityUp,
                		cCutType,
                		iCutPrintNum
                	) b ON a.cMOLotNo = b.cOrderNo 
                    LEFT JOIN (SELECT cSpecifution,SUM ( dReqQty ) AS dReqQty1 ,SUM ( dfReqQty ) AS dfReqQty 
                                FROM APS_ValidBOM  
                                WHERE cOrderType = '3' AND cOrderNo = '{0}'
                                GROUP BY cSpecifution) c ON b.cSpecifution = c.cSpecifution
                WHERE a.cLotType = '3' and a.cMOLotNo = '{1}' and a.cStatusNo in ('0051','0062','0063') order by b.dWidth;",para,para);

                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("数据为空");
                }
                list.Add(strPicYZ);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //人工选择已切仓库等数据查询
        public List<string> WareHouseDataQuerys(string para)
        {
            List<string> list = new List<string>();
            try
            {
                List<Dictionary<string, string>> pairs = new List<Dictionary<string, string>>();
                Dictionary<string, string> keys = new Dictionary<string, string>();
                var sql = string.Format(@"select cWhName,cWhCode from WMS_WareHouse_V");
                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                keys.Add("WMS_WareHouse_V", strPicYZ);

                if(para == "Z61")
                {
                    sql = string.Format(@"SELECT iSerialNo,cDate FROM APS_SerialNo WHERE cType='CG';");
                }else
                {
                    sql = string.Format(@"SELECT iSerialNo,cDate FROM APS_SerialNo  WHERE cType='C';");
                }
                strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                keys.Add("APS_SerialNo", strPicYZ);

                var today = CommonMethod.Instance.getTime3();
                var msec = today.Split('-');
                var ymd1 = msec[0].Substring(2) + msec[1] + msec[2];
                // 流水号
                int hisSerialNo = 0;
                Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(strPicYZ);
                for(int i = 0; i< jArray.Count; i++)
                {
                    var k = jArray[i];
                    if (k["cDate"].ToString() == today)
                    {
                        hisSerialNo = int.Parse(k["iSerialNo"].ToString());
                        break;
                    }
                    else
                    {
                        if (para == "Z61")
                        {
                            hisSerialNo = 500;
                        }
                        else
                        {
                            hisSerialNo = 0;
                        }
                    }
                }
                //裁切批号
                var cutFlowNo = "C" + ymd1 + CommonMethod.Instance.GenerateID(++hisSerialNo, "000");
                var sqlInsert = "";

                if (para == "Z61")
                {
                    sqlInsert = string.Format(@"UPDATE APS_SerialNo SET cDate = '{0}', iSerialNo = {1} WHERE cType = 'CG';",today,hisSerialNo);
                }
                else
                {
                    sqlInsert = string.Format(@"UPDATE APS_SerialNo SET cDate = '{0}', iSerialNo = {1} WHERE cType = 'C';", today, hisSerialNo);
                }

                var connstr = SqlHelper.GetConnSting();
                SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, sqlInsert);
                keys.Add("CuttingNum", cutFlowNo);
                pairs.Add(keys);

                string json = JsonConvert.SerializeObject(pairs);
                list.Add(json);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //人工选择已切数据查询
        public List<string> QueryData(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string value3 = k["value3"].ToString();
                string value4 = k["value4"].ToString();
                string value5 = k["value5"].ToString();
                string value2 = k["value2"].ToString();
                string warehousecodes = k["warehousecodes"].ToString();
                string warehousecodesJson = k["warehousecodesJson"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();
                var matType = "";
                switch (cProductDepNo)
                {
                    case "Z21":
                        matType = ""; //液态产品
                        break;
                    case "Z61":
                        matType = ""; //固态产品
                        break;
                }

                string sqlAll = "";

                string warehousecodesstr = "";
                string sql = "1 = 1";
                Newtonsoft.Json.Linq.JArray warehousecodesArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(warehousecodesJson);
                for (int i = 0; i < warehousecodesArr.Count; i++)
                {
                    var k1 = warehousecodesArr[i];
                    warehousecodesstr += k1 + ",";
                }
                if(warehousecodesstr.Length > 0)
                {
                    warehousecodesstr = warehousecodesstr.Substring(0, warehousecodesstr.Length - 1);

                    sql = string.Format(@"a.warehousecode in ({0})",warehousecodesstr);
                }

                var sql1 = string.Format(@"SELECT
                	a.cMatNo,
                	a.cMatName,
                	a.cSpecifucation,
					case
                		--WHEN a.cMatName LIKE '%电解纸%' THEN (ISNULL( a.dCurrentQty, 0 )*c.副计量单位用量 - ISNULL( b.dReqQty, 0 ))
						--ELSE (ISNULL( a.dCurrentQty, 0 )- ISNULL( b.dReqQty, 0 ))
						WHEN a.cMatName LIKE '%电解纸%' THEN (ISNULL( a.dCurrentQty, 0 )*c.副计量单位用量)
						ELSE (ISNULL( a.dCurrentQty, 0 ))
					END as dCurrentQty1,
                	case
						WHEN a.cMatName LIKE '%电解纸%' THEN a.dCurrentQty
						ELSE a.iNum
					END AS dCurrentQty,
                	a.cCapacity,
                	a.cBatchNumber,
                	case
					    WHEN (d.dWidth>0 OR d.dWidth IS NOT NULL) THEN d.dWidth
					    WHEN a.dEffWidth IS NOT NULL THEN a.dEffWidth
					    WHEN (a.cCateNo = 'Y0101' OR a.cCateNo = 'Y0191') THEN '480'
					    WHEN a.cCateNo = 'Y0102' THEN '492'
					    WHEN (a.cCateNo = 'Y0103' AND a.cSpecifucation like '%ADS%')THEN '517'
						WHEN (a.cCateNo = 'Y0103' AND a.cSpecifucation not like '%ADS%')THEN '572'
					END as dEffWidth,
                	--( ISNULL( a.dCurrentQty, 0 )*c.副计量单位用量 - ISNULL( b.dReqQty, 0 ) ) / a.dEffWidth* 1000 AS dLength,
                	( ISNULL( a.dCurrentQty, 0 )*c.副计量单位用量 ) / a.dEffWidth* 1000 AS dLength,
                	case
						WHEN a.cMatName LIKE '%电解纸%'THEN c.副计量单位用量
						ELSE a.cdz
					END AS 单重,
                	a.cxx,
                	a.csx,
                	a.warehousecode,
                	a.cCateNo,
                	e.cWhName
                FROM
                	APS_MatStock_V a
                	LEFT JOIN (SELECT 成品编码,副计量单位用量,副计量单位名称 FROM View_Bom GROUP BY 成品编码,副计量单位用量,副计量单位名称) c ON a.cMatNo = c.成品编码
                	LEFT JOIN (SELECT cMatNo,cBatchNumber,SUM(dReqQty) as dReqQty 
                	            FROM APS_ValidBOM 
                	            WHERE cOrderType='3' AND cPickFlag='0' AND cCutFlag='0'
                	            GROUP BY cMatNo,cBatchNumber) b ON a.cMatNo=b.cMatNo AND a.cBatchNumber=b.cBatchNumber 
                	LEFT JOIN PMS_UnCutStock d ON a.cMatNo = d.cMatNo AND a.cBatchNumber = d.cBatchNumber
                	LEFT JOIN WMS_WareHouse_V e ON e.cWhCode = a.warehousecode
                WHERE
                	{0} AND a.cMatName LIKE '未切%{1}%' AND (a.dCurrentQty>0.001 OR a.iNum>0.001) ",sql,matType);

                if (value3 != "")
                {
                    sql1 += string.Format(@"AND a.cSpecifucation LIKE '%{0}%' ",value3);
                }
                if (value2 == "正箔")
                {
                    sql1 += string.Format(@"AND a.cCateNo in ('Y0101', 'Y0191') ");
                }
                if (value2 == "负箔")
                {
                    sql1 += "AND a.cCateNo = 'Y0102' ";
                }
                if (value2 == "电解纸")
                {
                    sql1 += "AND a.cCateNo = 'Y0103' ";
                }

                if (value4 != "")
                {
                    sql1 += string.Format(@"and a.cBatchNumber like '%{0}%' ",value4);
                }

                if (value5 != "")
                {
                    sql1 += string.Format(@"and a.cMatNo like '%{0}%' ",value5);
                }

                var sqlStr1 = string.Format(@"SELECT
                        a.cMatNo,
                    	a.cMatName,
                    	a.cSpecifucation,

                        case
                            WHEN a.cMatName LIKE '%电解纸%' THEN(ISNULL(a.dCurrentQty, 0) * c.副计量单位用量)

                            ELSE(ISNULL(a.dCurrentQty, 0))

                        END as dCurrentQty1,
                    	case
                            WHEN a.cMatName LIKE '%电解纸%' THEN a.dCurrentQty
                            ELSE a.iNum
                        END AS dCurrentQty,
                        a.cCapacity,
                    	a.cBatchNumber,
                    	case
                            WHEN(d.dWidth > 0 OR d.dWidth IS NOT NULL) THEN d.dWidth
                            WHEN a.dEffWidth IS NOT NULL THEN a.dEffWidth

                            WHEN(a.cCateNo = 'Y0101' OR a.cCateNo = 'Y0191') THEN '480'

                            WHEN a.cCateNo = 'Y0102' THEN '492'

                            WHEN(a.cCateNo = 'Y0103' AND a.cSpecifucation like '%ADS%')THEN '517'

                            WHEN(a.cCateNo = 'Y0103' AND a.cSpecifucation not like '%ADS%')THEN '572'

                        END as dEffWidth,
                    	(ISNULL(a.dCurrentQty, 0) * c.副计量单位用量) / a.dEffWidth * 1000 AS dLength,

                        case
                            WHEN a.cMatName LIKE '%电解纸%'THEN c.副计量单位用量
                            ELSE a.cdz
                        END AS 单重,
                        a.cxx,
                    	a.csx,
                    	a.warehousecode,
                    	a.cCateNo,
                    	e.cWhName
                    FROM
                        APS_AOGPlanSub a
                        LEFT JOIN(SELECT 成品编码, 副计量单位用量, 副计量单位名称 FROM View_Bom GROUP BY 成品编码, 副计量单位用量, 副计量单位名称) c ON a.cMatNo = c.成品编码
                        LEFT JOIN(SELECT cMatNo, cBatchNumber, SUM(dReqQty) as dReqQty
                                    FROM APS_ValidBOM
                                    WHERE cOrderType = '3' AND cPickFlag = '0' AND cCutFlag = '0'
                                    GROUP BY cMatNo,cBatchNumber) b ON a.cMatNo = b.cMatNo AND a.cBatchNumber = b.cBatchNumber
                        LEFT JOIN PMS_UnCutStock d ON a.cMatNo = d.cMatNo AND a.cBatchNumber = d.cBatchNumber
                        LEFT JOIN WMS_WareHouse_V e ON e.cWhCode = a.warehousecode
                    WHERE {0} AND a.cMatName LIKE '未切%{1}%' AND(a.dCurrentQty > 0.001 OR a.iNum > 0.001)",sql,matType);

                if (value3 != "")
                {
                    sqlStr1 += string.Format(@"AND a.cSpecifucation LIKE '%{0}%' ", value3);
                }
                if (value2 == "正箔")
                {
                    sqlStr1 += string.Format(@"AND a.cCateNo in ('Y0101', 'Y0191') ");
                }
                if (value2 == "负箔")
                {
                    sqlStr1 += "AND a.cCateNo = 'Y0102' ";
                }
                if (value2 == "电解纸")
                {
                    sqlStr1 += "AND a.cCateNo = 'Y0103' ";
                }

                if (value4 != "")
                {
                    sqlStr1 += string.Format(@"and a.cBatchNumber like '%{0}%' ", value4);
                }

                if (value5 != "")
                {
                    sqlStr1 += string.Format(@"and a.cMatNo like '%{0}%' ", value5);
                }


                var sqlStr2 = string.Format(@"SELECT
                a.cMatNo,
            	a.cMatName,
            	a.cSpecifucation,

                case
                    WHEN a.cMatName LIKE '%电解纸%' THEN(ISNULL(a.dCurrentQty, 0) * c.副计量单位用量 - ISNULL(b.dReqQty, 0))

                    ELSE(ISNULL(a.dCurrentQty, 0) - ISNULL(b.dReqQty, 0))

                END as dCurrentQty1,
            	case
                    WHEN a.cMatName LIKE '%电解纸%' THEN a.dCurrentQty
                    ELSE a.iNum
                END AS dCurrentQty,
                a.cCapacity,
            	a.cBatchNumber,
            	case
                    WHEN(d.dWidth > 0 OR d.dWidth IS NOT NULL) THEN d.dWidth
                    WHEN a.dEffWidth IS NOT NULL THEN a.dEffWidth

                    WHEN(a.cCateNo = 'Y0101' OR a.cCateNo = 'Y0191') THEN '480'

                    WHEN a.cCateNo = 'Y0102' THEN '492'

                    WHEN(a.cCateNo = 'Y0103' AND a.cSpecifucation like '%ADS%')THEN '517'

                    WHEN(a.cCateNo = 'Y0103' AND a.cSpecifucation not like '%ADS%')THEN '572'

                END as dEffWidth,
            	(ISNULL(a.dCurrentQty, 0) * c.副计量单位用量 - ISNULL(b.dReqQty, 0)) / a.dEffWidth * 1000 AS dLength,

                case
                    WHEN a.cMatName LIKE '%电解纸%'THEN c.副计量单位用量
                    ELSE a.cdz
                END AS 单重,
                a.cxx,
            	a.csx,
            	a.warehousecode,
            	a.cCateNo,
            	e.cWhName
            FROM
                APS_AOGPlanSub_V a
                LEFT JOIN(SELECT 成品编码, 副计量单位用量, 副计量单位名称 FROM View_Bom GROUP BY 成品编码, 副计量单位用量, 副计量单位名称) c ON a.cMatNo = c.成品编码
                LEFT JOIN(SELECT cMatNo, cBatchNumber, SUM(dReqQty) as dReqQty
                            FROM APS_ValidBOM
                            WHERE cOrderType = '3' AND cPickFlag = '0' AND cCutFlag = '0'
                            GROUP BY cMatNo,cBatchNumber) b ON a.cMatNo = b.cMatNo AND a.cBatchNumber = b.cBatchNumber
                LEFT JOIN PMS_UnCutStock d ON a.cMatNo = d.cMatNo AND a.cBatchNumber = d.cBatchNumber
                LEFT JOIN WMS_WareHouse_V e ON e.cWhCode = a.warehousecode
            WHERE {0} AND a.cMatName LIKE '未切%{1}%' AND(a.dCurrentQty > 0.001 OR a.iNum > 0.001) ",sql,matType);

                if (value3 != "")
                {
                    sqlStr2 += string.Format(@"AND a.cSpecifucation LIKE '%{0}%' ", value3);
                }
                if (value2 == "正箔")
                {
                    sqlStr2 += string.Format(@"AND a.cCateNo in ('Y0101', 'Y0191') ");
                }
                if (value2 == "负箔")
                {
                    sqlStr2 += "AND a.cCateNo = 'Y0102' ";
                }
                if (value2 == "电解纸")
                {
                    sqlStr2 += "AND a.cCateNo = 'Y0103' ";
                }

                if (value4 != "")
                {
                    sqlStr2 += string.Format(@"and a.cBatchNumber like '%{0}%' ", value4);
                }

                if (value5 != "")
                {
                    sqlStr2 += string.Format(@"and a.cMatNo like '%{0}%' ", value5);
                }


                sqlAll = sql1 + " union " + sqlStr1 + " union " + sqlStr2;

                //string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sqlAll));
                DataTable dt = CommonMethod.Instance.selectDatabase(sqlAll);
                DataRow[] row = dt.Select("1 = 1");
                DataTable newData = new DataTable();
                newData = dt.Clone();

                for (int i = 0; i < row.Length; i++)
                {
                    var cSpecifucation = row[i]["cSpecifucation"].ToString();
                    if(cSpecifucation.Contains("270U50") || cSpecifucation.Contains("270U60") || cSpecifucation.Contains("255U40") || cSpecifucation.Contains("270U40"))
                    {
                        row[i]["dEffWidth"] = "492";
                    }
                    if (cSpecifucation.Contains("4R0H20"))
                    {
                        row[i]["dEffWidth"] = "490";
                    }
                    //固态
                    if (cSpecifucation == "F0000050044-030G01XX")
                    {
                        row[i]["dEffWidth"] = "115";
                    }
                    if (cSpecifucation == "F00000500NN-020F2001" || cSpecifucation == "F00000500NN-030F3001" || cSpecifucation == "F00000500NN-040F4001" || cSpecifucation == "F00000500NN-030F3006")
                    {
                        row[i]["dEffWidth"] = "495";
                    }
                    if (cSpecifucation == "F00000500NN-020F2011" || cSpecifucation == "F00000500NN-030F3011")
                    {
                        row[i]["dEffWidth"] = "490";
                    }
                    if (cSpecifucation == "F00000500YY-030CP01X")
                    {
                        row[i]["dEffWidth"] = "430";
                    }
                    if (cSpecifucation == "PX0580-60ADS40" || cSpecifucation == "PX0580-47ADS30")
                    {
                        row[i]["dEffWidth"] = "518";
                    }
                    if (cSpecifucation == "PM0580-50HKMS35")
                    {
                        row[i]["dEffWidth"] = "512";
                    }

                    DataRow row1 = row[i];
                    newData.Rows.Add(row1.ItemArray);
                }

                string strPicYZ = JsonConvert.SerializeObject(newData);
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("数据为空");
                }
                list.Add(strPicYZ);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //更新比容
        public List<string> UpdateCxx(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string csx = k["csx"].ToString();
                string cxx = k["cxx"].ToString();
                string cBatchNumber = k["cBatchNumber"].ToString();
                var sql = string.Format(@"select * from APS_AOGPlanSub_V where cBatchNumber = '{0}';",cBatchNumber);
                DataTable dt = CommonMethod.Instance.selectDatabase(sql);
                DataRow[] row = dt.Select("1 = 1");
                if(row.Length > 0)
                {
                    if(row.Length == 1)
                    {
                        list.Add(row[0]["dQty"].ToString());
                        var updatesql = string.Format(@"update [192.168.1.5].[UFDATA_168_2013].[dbo].[PU_ArrivalVouchs] set cBatchProperty2 = {0} ,cBatchProperty3 = {1} where Autoid = '{2}';", cxx, csx, row[0]["Autoid"].ToString());
                        var connstr = SqlHelper.GetConnSting();
                        SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, updatesql);
                    }
                    else
                    {
                        throw new Exception("此批号在erp数据库中有多条数据");
                    }
                }else
                {
                    throw new Exception("此规格不存在");
                }
            }
            catch(Exception e)
            {
                list.Add("Error" + e.Message);
            }
            return list;
        }

        //查询表格2 数据
        public List<string> QueryData2(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string value6 = k["value6"].ToString();
                string value9 = k["value9"].ToString();
                string value10 = k["value10"].ToString();
                string value11 = k["value11"].ToString();
                var sql = string.Format(@"SELECT a.cMatNo,a.cMatName,a.cSpecifucation,a.dWidth,a.cCapacity,b.dCurrentQty FROM APS_MatData_V a 
          LEFT JOIN APS_MatStock_V b ON a.cMatNo = b.cMatNo
          where a.cMatName LIKE '已切%' ");

                if (value6 != "")
                {
                    sql += string.Format(@" and a.cSpecifucation LIKE '%{0}%' ",value6);
                }
                if (value11 != "")
                {
                    sql += string.Format(@" and a.cSpecifucation LIKE '%{0}%' ",value11);
                }

                if (value9 != "")
                {
                    sql += string.Format(@" and a.cMatNo = '{0}' ",value9);
                }

                if (value10 != "")
                {
                    sql += string.Format(@" and a.dwidth = '{0}' ",value10);
                }
                sql += "order by a.dwidth,a.cMatNo ";
                if (value6 != "" || value9 != "" || value10 != "")
                {
                    string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                    if (strPicYZ.Equals("[]"))
                    {
                        throw new Exception("数据为空");
                    }
                    list.Add(strPicYZ);
                }else
                {
                    throw new Exception("数据为空");
                }
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //数据保存
        public List<string> saveData(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string cMOLotNo = k["cMOLotNo"].ToString();
                string cMatNo = k["cMatNo"].ToString();
                string dLotQty = k["dLotQty"].ToString();
                string cBatchNumber = k["cBatchNumber"].ToString();
                string dLength = k["dLength"].ToString();
                string dLotQty1 = k["dLotQty1"].ToString();
                string cdz = k["cdz"].ToString();
                string cxx = k["cxx"].ToString();
                string csx = k["csx"].ToString();
                string cStaUpdater = k["csx"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();
                string json3 = k["csx"].ToString();
                string dEffWidth = k["dEffWidth"].ToString();
                string today = CommonMethod.Instance.getTime3();

                Newtonsoft.Json.Linq.JArray Dx = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(json3);
                var sql1 = string.Format(@"INSERT INTO APS_ProLotOrder(cMOLotNo, dMakePlanDate, cStatusNo, cStaUpdater, cMatNo,
                dLotQty, cLotType, cOFlag, cProductDepNo, isPrint, cBarCode, QcResult, BECategory, PickFlag,
                    cBatchNumber, isSync, cMakeTime, dEffWidth, dTotalWeight, ddz, cxx, csx) VALUES ");

                var sql2 = string.Format(@"INSERT INTO APS_ValidBOM(cOrderNo, cOrderType, cMatNo, cInOutType, cMatandResNo, cMatandResName,
                cMatandResType, dPlanQty, dWidth, dLength, cSpecifution, cBatchNumber, cUnit, dReqQty,
                                cCapacityLow, cCapacityUp, dVoltage, Unit, cPickFlag, cCutType, cCutFlag, iCutPrintNum) VALUES ");

                sql1 += string.Format(@"('{0}','{1}','0062','{2}','{3}',
                    '{4}','3','0','{5}','0',
                    'WO-{6}','5','0','0','{7}','0','{8}',{9},{10},{11},{12},{13}) ", cMOLotNo, today, cStaUpdater, cMatNo, dLotQty, cProductDepNo, cMOLotNo, cBatchNumber, today, dEffWidth, dLotQty1,
                    cdz, cxx, csx);

                for(int i = 0; i < Dx.Count; i++)
                {
                    var cCapacity = Dx[i]["cCapacity2"].ToString();
                    var cCapacityLow = "";
                    var cCapacityUp = "";

                    if(cCapacity != "")
                    {
                        if (cCapacity.Contains("~"))
                        {
                            cCapacityLow = cCapacity.Split('~')[0];
                            cCapacityUp = cCapacity.Split('~')[1];
                        }
                        if (cCapacity.Contains("-"))
                        {
                            cCapacityLow = cCapacity.Split('-')[0];
                            cCapacityUp = cCapacity.Split('-')[1];
                        }
                    }
                    double dPlanQty = double.Parse((Dx[i]["dPlanQty"].ToString()));
                    double dWidth = double.Parse((Dx[i]["dWidth2"].ToString()));
                    double dLength1 = double.Parse((Dx[i]["dLength2"].ToString()));
                    double dReqQty = double.Parse((Dx[i]["dReqQty"].ToString()));
                    double pagecount = 0;
                    if (Dx[i]["page2"].ToString() != "")
                    {
                         pagecount = double.Parse((Dx[i]["page2"].ToString()));
                    }
                    if (i < Dx.Count - 1)
                    {
                        sql2 += string.Format(@"('{0}','3','{1}','output','{2}',NULL,
                    NULL,{3},{4},{5},'{6}','{7}',NULL,{8},
                    '{9}','{10}',NULL,NULL,'0','{11}','1',{12}), ", cMOLotNo, cMatNo, Dx[i]["cMatNo"].ToString(), dPlanQty, dWidth, dLength, Dx[i]["cSpecifucation"].ToString(), cBatchNumber,
                    dReqQty, cCapacityLow, cCapacityUp, Dx[i]["cCutType"].ToString(), pagecount);
                    }

                    if(i == Dx.Count - 1)
                    {
                        sql2 += string.Format(@"('{0}','3','{1}','output','{2}',NULL,
                    NULL,{3},{4},{5},'{6}','{7}',NULL,{8},
                    '{9}','{10}',NULL,NULL,'0','{11}','1',{12}) ", cMOLotNo, cMatNo, Dx[i]["cMatNo"].ToString(), dPlanQty, dWidth, dLength, Dx[i]["cSpecifucation"].ToString(), cBatchNumber,
                    dReqQty, cCapacityLow, cCapacityUp, Dx[i]["cCutType"].ToString(), pagecount);
                    }

                    if (cProductDepNo == "Z21")
                    {
                        YTCQSort(cMOLotNo);
                    }
                    if (cProductDepNo == "Z61")
                    {
                        YTCQSortGT(cMOLotNo);
                    }
                    list.Add("success");
                }
            }
            catch(Exception e)
            {
                list.Add("Error" + e.Message);
            }
            return list;
        }

        private void YTCQSort(string cMOLotNo)
        {
            try
            {
                var sqlSort = string.Format(@"select* from APS_ValidBOM where cOrderNo = '{0}' order by dPlanQty desc;",cMOLotNo);
                var tb = CommonMethod.Instance.selectDatabase(sqlSort);
                var row = tb.Select("1 = 1");
                string ID = row[0]["ID"].ToString();
                var sql = "";
                if (row.Length > 0)
                {
                    if(row.Length == 1)
                    {
                        sql += string.Format(@"Update APS_ValidBOM set iCutPrintNum = 4 where Id = {0};", ID);
                    }else
                    {
                        bool isOK = false;
                        var dPlanQty = int.Parse(row[0][12].ToString());
                        for (var ii = 0; ii < row.Length; ii++)
                        {
                            var dPlanQty1 = int.Parse(row[ii][12].ToString());
                            if (dPlanQty != dPlanQty1)
                            {
                                isOK = true;
                                break;
                            }
                        }
                        if (isOK)
                        {
                            for (var i = 0; i < 2; i++)
                            {
                                ID = row[i][0].ToString();
                                sql += string.Format(@"Update APS_ValidBOM set iCutPrintNum =2 where Id = {0};", ID);
                            }
                        }
                        else
                        {
                            YTCQSort1(cMOLotNo);
                        }
                    }

                }else
                {
                    throw new Exception("查询失败");
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        //如果片数一样，按规格排序
        private void YTCQSort1(string cMOLotNo)
        {
            try
            {
                var sqlSort = string.Format(@"select* from APS_ValidBOM where cOrderNo = '{0}' order by cSpecifution desc;",cMOLotNo);
                var tb = CommonMethod.Instance.selectDatabase(sqlSort);
                var row = tb.Select("1 = 1");
                string ID = "";
                var sql = "";
                if(row.Length > 0)
                {
                    for (var i = 0; i < 2; i++)
                    {
                        ID = row[i][0].ToString();
                        sql += string.Format(@"Update APS_ValidBOM set iCutPrintNum =2  where Id = {0};", ID);
                    }
                }else
                {
                    throw new Exception("查询失败");
                }

            }
            catch(Exception e)
            {
                throw e;
            }
        }


        private void YTCQSortGT(string cMOLotNo)
        {
            try
            {
                var sqlSort = string.Format(@"select* from APS_ValidBOM where cOrderNo = '{0}' order by dPlanQty desc;", cMOLotNo);
                var tb = CommonMethod.Instance.selectDatabase(sqlSort);
                var row = tb.Select("1 = 1");
                string ID = row[0]["ID"].ToString();
                var sql = "";
                if (row.Length > 0)
                {
                    if (row.Length == 1)
                    {
                        sql += string.Format(@"Update APS_ValidBOM set iCutPrintNum = 2 where Id = {0};", ID);
                    }
                    else
                    {
                        bool isOK = false;
                        var dPlanQty = int.Parse(row[0][12].ToString());
                        for (var ii = 0; ii < row.Length; ii++)
                        {
                            var dPlanQty1 = int.Parse(row[ii][12].ToString());
                            if (dPlanQty != dPlanQty1)
                            {
                                isOK = true;
                                break;
                            }
                        }
                        if (isOK)
                        {
                            for (var i = 0; i < 2; i++)
                            {
                                ID = row[i][0].ToString();
                                sql += string.Format(@"Update APS_ValidBOM set iCutPrintNum = 1 where Id = {0};", ID);
                            }
                        }
                        else
                        {
                            YTCQSort1GT(cMOLotNo);
                        }
                    }

                }
                else
                {
                    throw new Exception("查询失败");
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        //如果片数一样，按规格排序
        private void YTCQSort1GT(string cMOLotNo)
        {
            try
            {
                var sqlSort = string.Format(@"select* from APS_ValidBOM where cOrderNo = '{0}' order by cSpecifution desc;", cMOLotNo);
                var tb = CommonMethod.Instance.selectDatabase(sqlSort);
                var row = tb.Select("1 = 1");
                string ID = "";
                var sql = "";
                if (row.Length > 0)
                {
                    for (var i = 0; i < 2; i++)
                    {
                        ID = row[i][0].ToString();
                        sql += string.Format(@"Update APS_ValidBOM set iCutPrintNum = 1  where Id = {0};", ID);
                    }
                }
                else
                {
                    throw new Exception("查询失败");
                }

            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<string> CuttingStatusChange(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var sql = "";
                var k = jArray[0];
                string cmo = k["cmo"].ToString();
                string len = k["len"].ToString();
                string[] cMo = cmo.Split(',');
                for(int i = 0; i < cMo.Length; i++)
                {
                    sql = string.Format(@"DECLARE @return_value int  EXEC  @return_value = [dbo].[CutOrderPubilsh]  @cutOrder = '{0}' SELECT  'ReturnValue' = @return_value", cMo[i]);
                    var connstr = SqlHelper.GetConnSting();
                    SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, sql);
                }
                list.Add("success");
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        public List<string> QueryDataOrder(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string value1 = k["value1"].ToString();
                string value2 = k["value2"].ToString();
                string value3 = k["value3"].ToString();
                string value4 = k["value4"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();

                DateTime Stime = DateTime.Parse(value3);
                DateTime Etime = DateTime.Parse(value4);

                string sTstr = Stime.ToString("yyyy-MM-dd HH:mm:ss");
                string eTstr = Etime.ToString("yyyy-MM-dd HH:mm:ss");
                if(Etime >= Stime)
                {
                    var sql = string.Format(@"select a.cMOLotNo,a.dMakePlanDate FROM APS_ProLotOrder a
                        LEFT JOIN APS_MatStock_V b ON a.cMatNo = b.cMatNo AND a.cBatchNumber = b.cBatchNumber
                        LEFT JOIN APS_AOGPlanSub_V c ON a.cMatNo = c.cMatNo AND a.cBatchNumber = c.cBatchNumber
                        WHERE a.cLotType = '3'
                        AND (b.cSpecifucation = '{0}' OR c.cSpecifucation = '{1}')
                        AND a.cMatNo = '{2}'
                        AND a.cProductDepNo = '{3}'
                        AND a.dMakePlanDate >= '{4}' 
                        AND a.dMakePlanDate <= '{5}' 
                        order by a.cMOLotNo;", value1, value1, value2, cProductDepNo, sTstr, eTstr);
                    string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                    if(strPicYZ.Equals("[]"))
                    {
                        throw new Exception("查询数据为空");
                    }
                    list.Add(strPicYZ);
                }
                else
                {
                    list.Add("结束日期小于开始日期,请重新选择!");
                }
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //查询已切信息
        public List<string> QueryCuttingOrderFirst(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"SELECT
                	a.cMOLotNo,
                	a.cMatNo,
                	a.cBegProDate,
                	a.cEndProDate,
                	NULLIF ( b.dPlanQty, 0 ) * NULLIF ( b.dWidth, 0 ) AS dEffWidth,
                	b.cSpecifution,
                	b.dReqQty,
                	c.dReqQty1,
                	c.dfReqQty,
                	ISNULL(c.dReqQty1,0) - ISNULL(c.dfReqQty,0) as cha,
                	NULLIF ( b.dPlanQty, 0 ) AS dPlanQty,
                	b.dWidth,
                	NULLIF ( b.dLength, 0 ) AS dLength,
                	b.cCapacityLow,
                	b.cCapacityUp,
                	b.cCutType,
                	CAST(b.iCutPrintNum AS VARCHAR) AS iCutPrintNum
                FROM
                	APS_ProLotOrder a
                	LEFT JOIN (
                	SELECT
                		cOrderNo,
                		SUM ( dReqQty ) AS dReqQty,
                		SUM ( dfReqQty ) AS dfReqQty,
                		SUM(dPlanQty) AS dPlanQty,
                		cSpecifution,
                		dWidth,
                		dLength,
                		cCapacityLow,
                		cCapacityUp,
                		cCutType,
                		iCutPrintNum
                	FROM
                		APS_ValidBOM 
                	where cOrderType = '3'
                	GROUP BY
                		cOrderNo,
                		cSpecifution,
                		dWidth,
                		dLength,
                		cCapacityLow,
                		cCapacityUp,
                		cCutType,
                		iCutPrintNum
                	) b ON a.cMOLotNo = b.cOrderNo 
                    LEFT JOIN (SELECT cSpecifution,SUM ( dReqQty ) AS dReqQty1 ,SUM ( dfReqQty ) AS dfReqQty 
                                FROM APS_ValidBOM  
                                WHERE cOrderType = '3' AND cOrderNo = '{0}'
                                GROUP BY cSpecifution) c ON b.cSpecifution = c.cSpecifution
                WHERE a.cLotType = '3' and a.cMOLotNo = '{1}'  order by b.dPlanQty desc,b.dWidth desc;", para,para);
               

                DataTable dt = CommonMethod.Instance.selectDatabase(sql);
                int len = dt.Rows.Count;

                DataRow dataRow = dt.NewRow();
                for (int i = 0; i < dataRow.ItemArray.Length; i++)
                {
                    dataRow[i] = DBNull.Value;
                }
                dt.Rows.Add(dataRow.ItemArray);
                dt.Rows.Add(dataRow.ItemArray);
                string strPicYZ = JsonConvert.SerializeObject(dt);
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(strPicYZ + len.ToString());
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //查询未切信息
        public List<string> QueryNotingData(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string cProductDepNo = k["cProductDepNo"].ToString();
                string cMOLotNo = k["cMOLotNo"].ToString();
                var sql = string.Format(@"select APS_ProLotOrder.dMakePlanDate,
                                APS_ProLotOrder.cStatusNo,
                                APS_ProLotOrder.cMOLotNo,
                                APS_ProLotOrder.cMatNo,
                                APS_ProLotOrder.dLotQty,
                                APS_ProLotOrder.dTotalWeight,
                                APS_ProLotOrder.cBegProDate,
                                APS_ProLotOrder.cEndProDate,
                                APS_ProLotOrder.cBatchNumber,
                                APS_ProLotOrder.cProductDepNo,
                                case 
                                    WHEN APS_ProLotOrder.cStatusNo = '0063' THEN APS_ProLotOrder.cStaUpdateTime
                                ELSE ''
                                END AS cStaUpdateTime,
                                case
                                    WHEN (APS_ProLotOrder.dLotQty - a.dAllReqQty)>=0 THEN (APS_ProLotOrder.dLotQty - a.dAllReqQty)
                                    ELSE 0
                                END as dQty,
                                APS_ProLotOrder.dEffWidth as dEffWidth1,
                                (APS_ProLotOrder.dEffWidth - a.dEffWidth) as dEffWidth2,
                                APS_ProLotOrder.dNetQty,
                                case
                                    when APS_MatStock_V.dCurrentQty is not null then APS_MatStock_V.dCurrentQty
                                    else APS_AOGPlanSub_V.dCurrentQty
                                end as dCurrentQty,
                                case
                                    when APS_MatStock_V.cSpecifucation is not null then APS_MatStock_V.cSpecifucation
                                    else APS_AOGPlanSub_V.cSpecifucation
                                end as cSpecifucation
                            FROM APS_ProLotOrder 
                                LEFT JOIN (SELECT cOrderNo,SUM(dReqQty) as dAllReqQty,SUM(dPlanQty*dWidth) as dEffWidth FROM APS_ValidBOM where cOrderType = '3' GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
                                LEFT JOIN APS_MatStock_V ON APS_ProLotOrder.cMatNo = APS_MatStock_V.cMatNo AND APS_ProLotOrder.cBatchNumber = APS_MatStock_V.cBatchNumber
                                LEFT JOIN APS_AOGPlanSub_V ON APS_ProLotOrder.cMatNo = APS_AOGPlanSub_V.cMatNo AND APS_ProLotOrder.cBatchNumber = APS_AOGPlanSub_V.cBatchNumber
                            WHERE 
                                APS_ProLotOrder.cLotType = '3'
                                AND APS_ProLotOrder.cProductDepNo = '{0}'
								AND APS_ProLotOrder.cMOLotNo = '{1}';",cProductDepNo,cMOLotNo);

                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(strPicYZ);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //保存修改
        public List<string> SaveCuttingData(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string dMakePlanDate = k["dMakePlanDate"].ToString();
                string cCutLotNo = k["cCutLotNo"].ToString();
                string cMatNo = k["cMatNo"].ToString(); 
                string cBatchNumber = k["cBatchNumber"].ToString();
                string cSpecifution = k["cSpecifution"].ToString();
                string dWidth = k["dWidth"].ToString();
                string dPlanQty = k["dPlanQty"].ToString();

                string dLength = k["dLength"].ToString();
                string dArea = k["dArea"].ToString();
                string cBegProDate = k["cBegProDate"].ToString();
                string cEndProDate = k["cEndProDate"].ToString();
                string cCutType = k["cCutType"].ToString();
                string iCutPrintNum = k["iCutPrintNum"].ToString();
                string ret = k["rets"].ToString();
                Newtonsoft.Json.Linq.JArray jArray1 = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(ret);
                if (cSpecifution == "")
                {
                    throw new Exception("数据不完整");
                }
                else if (dWidth == "")
                {
                    throw new Exception("数据不完整");
                }
                else if (dLength == "")
                {
                    throw new Exception("数据不完整");
                }
                else if (dPlanQty == "")
                {
                    throw new Exception("数据不完整");
                }
                else if (dArea == "")
                {
                    throw new Exception("数据不完整");
                }

                var sqlString = string.Format(@"update APS_ValidBOM  
                        set APS_ValidBOM.dWidth = '{0}',
                            APS_ValidBOM.dPlanQty ='{1}',
                            APS_ValidBOM.cCutType = '{2}',
                            APS_ValidBOM.iCutPrintNum = '{3}',
                            APS_ValidBOM.dReqQty = '{4}',
                            APS_ValidBOM.dLength = '{5}'
                    FROM APS_ValidBOM
                        LEFT JOIN APS_ProLotOrder ON APS_ValidBOM.cOrderNo = APS_ProLotOrder.cMOLotNo
                    where 
                        APS_ProLotOrder.dMakePlanDate = '{6}'
                        AND APS_ValidBOM.cOrderNo = '{7}'
                        AND APS_ValidBOM.cSpecifution = '{8}'
                        AND APS_ValidBOM.cMatNo = '${9}' 
                        AND cOrderType = '3';", dWidth, dPlanQty, cCutType, iCutPrintNum, dArea, dLength, dMakePlanDate, cCutLotNo, cSpecifution, cMatNo);

                 for(int i = 0; i < jArray1.Count -2; i++)
                {
                    var j = jArray1[i];
                    var cSpecifution1 = j["cSpecifution"].ToString() ;
                    if (cSpecifution1 != cSpecifution)
                    {
                        double dReqQty1 = double.Parse(j["dWidth"].ToString()) * double.Parse(dLength) * double.Parse(j["dPlanQty"].ToString()) * 0.001;
                        sqlString += string.Format(@"update APS_ValidBOM
                        set dLength = '{0}',
                        dReqQty = '{1}'
                        where APS_ValidBOM.cOrderNo = '{2}'
                        AND APS_ValidBOM.cMatNo = '{3}' 
                        AND APS_ValidBOM.cSpecifution = '{4}'
                        AND cOrderType = '3';", dLength, dReqQty1, cCutLotNo, cMatNo, cSpecifution1);
                    }
                }
                var connstr = SqlHelper.GetConnSting();
                SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, sqlString);
                list.Add("success");
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //查询增加裁切计划模块QTY
        public List<string> QueryQty(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"select
            APS_ProLotOrder.cMOLotNo,
            APS_ProLotOrder.cMatNo,
            APS_ProLotOrder.dLotQty,
            APS_ProLotOrder.cProductDepNo,
            case
                WHEN (APS_ProLotOrder.dLotQty - a.dAllReqQty)>=0 THEN (APS_ProLotOrder.dLotQty - a.dAllReqQty)
                ELSE 0
            END as dQty
        FROM APS_ProLotOrder 
            LEFT JOIN (SELECT cOrderNo,SUM(dReqQty) as dAllReqQty FROM APS_ValidBOM GROUP BY cOrderNo) a ON APS_ProLotOrder.cMOLotNo = a.cOrderNo
            LEFT JOIN APS_MatStock_V ON APS_ProLotOrder.cMatNo = APS_MatStock_V.cMatNo AND APS_ProLotOrder.cBatchNumber = APS_MatStock_V.cBatchNumber
        WHERE 
            APS_ProLotOrder.cLotType = '3'
            AND APS_ProLotOrder.cMOLotNo = '{0}';",para);
                string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (strPicYZ.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(strPicYZ);

            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //增加裁切数据保存
        public List<string> SaveAddData(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string cCutLotNo = k["cCutLotNo"].ToString();
                string cMatNo = k["cMatNo"].ToString();
                string cSpecifution = k["cSpecifution"].ToString();
                string dWidth = k["dWidth"].ToString();
                string dPiece = k["dPiece"].ToString();
                string dLength = k["dLength"].ToString();
                string dArea = k["dArea"].ToString();
                string cMatandResNo = k["cMatandResNo"].ToString();
                string cBatchNumber = k["cBatchNumber"].ToString();
                bool tj = true;
                if (cSpecifution == "")
                {
                    tj = false;
                    throw new Exception("数据不完整");
                    //return;
                }
                else if (dWidth == "")
                {
                    tj = false;
                    throw new Exception("数据不完整");
                    
                    //return;
                }
                else if (dLength == "")
                {
                    tj = false;
                    throw new Exception("数据不完整");
                   
                    //return;
                }
                else if (dPiece == "")
                {
                    tj = false;
                    throw new Exception("数据不完整");
                   
                    ////return;
                }
                else if (dArea == "")
                {
                    tj = false;
                    throw new Exception("数据不完整");
                    
                    //return;
                }
                if (cMatandResNo == "")
                {
                    tj = false;
                    throw new Exception("数据不完整");
                    
                    //return;
                }

                if (cSpecifution != "" && dWidth != "" && dLength != "" && dPiece != "" && dArea != "" && tj)
                {
                    var sqlo = string.Format(@"select * from APS_ValidBOM where cOrderNo = '{0}' AND dWidth = '{1}';", cCutLotNo, dWidth);
                    var sqlString = string.Format(@"insert into APS_ValidBOM
                        (cOrderNo,cOrderType,cMatNo,cInOutType,cMatandResNo,cMatandResName,cMatandResType,dPlanQty,dWidth,dLength,cSpecifution,cBatchNumber,dReqQty)  
                        values('{0}','3','{1}','output','{2}','','','{3}','{4}','{5}','{6}','{7}','{8}');", cCutLotNo, cMatNo, cMatandResNo, dPiece, dWidth, dLength, cSpecifution, cBatchNumber, dArea);
                    string strPicYZ = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sqlo));
                    if(strPicYZ.Equals("[]"))
                    {
                        var connstr = SqlHelper.GetConnSting();
                        SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, sqlString);
                        list.Add("success");
                    }else
                    {
                        throw new Exception("所输入宽度已存在！");
                    }
                }
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        public List<string> deleteCuttingData(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string cSpecifution = k["cSpecifution"].ToString();
                string cOrderNo = k["cOrderNo"].ToString();
                var sql = string.Format(@"delete from APS_ValidBOM where cSpecifution='{0}' AND cOrderNo = '{1}' AND cOrderType = '3';",cSpecifution,cOrderNo);
                var connstr = SqlHelper.GetConnSting();
                SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, sql);
                list.Add("success");
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        public List<string> deleteMainData(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = "";
                string[] stringsArr = para.Split(',');
                for(int i = 0; i < stringsArr.Length; i++)
                {
                    sql += string.Format(@"delete from APS_ValidBOM where cOrderNo='{0}' and cOrderType = '3';", stringsArr[i]);
                    sql += string.Format(@"delete from APS_ProLotOrder where cMOLotNo = '{0}' and cLotType = '3';", stringsArr[i]);
                    sql += string.Format(@"delete from APS_CutProNo where cCutLotNo like '%{0}%';", stringsArr[i]);
                    sql += string.Format(@"delete from APS_CutProLotOrder where cOrderNo = '{0}'", stringsArr[i]);
                }
                var connstr = SqlHelper.GetConnSting();
                SqlHelper.ExecuteNonQuery(connstr, CommandType.Text, sql);
                list.Add("success");
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }
    }
}